package com.google.code.ptrends.common.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import org.apache.log4j.Logger;

import com.google.code.ptrends.common.entities.Item;
import com.google.code.ptrends.common.entities.ItemCategory;
import com.google.code.ptrends.common.entities.ItemClass;
import com.google.code.ptrends.common.entities.Manufacturer;
import com.google.code.ptrends.common.entities.Price;
import com.google.code.ptrends.common.entities.Property;
import com.google.code.ptrends.common.entities.Session;
import com.google.code.ptrends.common.entities.Supplier;

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;

public class DerbyDB {

    private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String URL = "jdbc:derby:memory:myDB;create=true";
    private static final String SHUTDOWN_URL = "jdbc:derby:memory:myDB;drop=true";

    private static final Logger LOG = Logger.getLogger(DerbyDB.class);

    private transient Connection conn;

    public DerbyDB() throws IOException, SQLException {
	createDB();
	try {
	    createTablesDB();
	} catch (SQLException e) {
	    LOG.error("Some problems with creating tables in database");
	    throw e;
	}
    }

    public Connection getConnection() {
	return conn;
    }

    public void shutdown() throws SQLException {
	try {
	    Class.forName(DRIVER);
	} catch (ClassNotFoundException ex) {
	    LOG.error("Class " + DRIVER + " not found", ex);
	}
	try {
	    conn = DriverManager.getConnection(SHUTDOWN_URL);
	    // conn.close();
	} catch (SQLException e) {
	    LOG.error("Error while shutting down database " + SHUTDOWN_URL, e);
	    if (!"08006".equals(e.getSQLState())) { // when derby in-memory is
						    // shutdown this exception
						    // is thrown
		throw e;
	    }
	}
    }

    public void dropData() throws SQLException {
	// prices, item_values, parameters, items, etl_rules_parameters, then
	// all other tables -- order is important due to foreign keys
	deleteRows("prices");
	deleteRows("item_values");
	deleteRows("parameters");
	deleteRows("etl_rules_parameters");
	deleteRows("etl_rules");
	deleteRows("items");
	deleteRows("classes");
	deleteRows("categories");
	deleteRows("manufacturers");
	deleteRows("suppliers");
	deleteRows("sessions");
	deleteRows("session_events");
	deleteRows("event_types");
	/*
	 * try { createTablesDB(); } catch (IOException e) { }
	 */
    }

    public void deployData(final String jsonFileName) throws IOException, SQLException {
	final JsonHelper jsonHelper = new JsonHelper();
	final Collection<Item> items = jsonHelper.loadItems(jsonFileName);

	saveCategories(items);
	saveClasses(items);
	saveManufacturers(items);
	saveParameters(items);
	saveSuppliers(items);

	// TODO now enumerate each item, insert it and then insert prices
	for (Item item : items) {
	    saveItem(item);
	}
    }

    public void deploySessionsData(final String jsonFileName) throws IOException, SQLException {
	final JsonHelper jsonHelper = new JsonHelper();
	final Collection<Session> sessions = jsonHelper.loadSessions(jsonFileName);
	saveEventTypes();
	saveSessions(sessions);
    }

    @SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
    public List<Row> readRows(String tableName) throws SQLException {
	List<Row> result = new ArrayList<Row>();

	String sqlQuery = "select * from " + tableName;
	PreparedStatement preparedStatement = conn.prepareStatement(sqlQuery);
	ResultSet resultSet = null;
	ResultSetMetaData metadata = null;
	try {
	    resultSet = preparedStatement.executeQuery();
	    metadata = resultSet.getMetaData();

	    while (resultSet.next()) {
		Row row = new Row();
		for (int i = 1; i <= metadata.getColumnCount(); ++i) {
		    String columnName = metadata.getColumnName(i);
		    Object value = resultSet.getObject(i);
		    String stringValue = null;
		    if (value != null) {
			stringValue = value.toString();
		    }

		    row.addValue(columnName, stringValue);
		}

		result.add(row);
	    }

	} catch (SQLException e) {
	    LOG.error("Error reading rows", e);
	    throw e;
	} finally {
	    if (preparedStatement != null) {
		preparedStatement.close();
	    }
	    if (resultSet != null) {
		resultSet.close();
	    }
	}

	return result;
    }

    public void executeSQL(final String sql) throws SQLException {
	StringTokenizer strTokenizer;
	Statement statement;
	strTokenizer = new StringTokenizer(sql, ";");
	statement = conn.createStatement();
	try {
	    while (strTokenizer.hasMoreTokens()) {
		final String nextToken = (String) strTokenizer.nextToken();
		statement.addBatch(nextToken);
	    }
	    statement.executeBatch();
	} finally {
	    statement.close();
	}
    }

    private void createDB() {
	try {
	    Class.forName(DRIVER);
	} catch (ClassNotFoundException ex) {
	    LOG.error("Class " + DRIVER + " not found", ex);
	}
	try {
	    conn = DriverManager.getConnection(URL);
	} catch (SQLException e) {
	    LOG.error("Problem with creating database " + URL, e);
	}
    }

    private void createTablesDB() throws SQLException, IOException {
	StringTokenizer strTokenizer;
	Statement statement;
	final String sql = DerbyDBSchema.getSchema();
	strTokenizer = new StringTokenizer(sql, ";");
	statement = conn.createStatement();
	try {
	    while (strTokenizer.hasMoreTokens()) {
		final String nextToken = (String) strTokenizer.nextToken();
		statement.addBatch(nextToken);
	    }
	    statement.executeBatch();
	} finally {
	    statement.close();
	}
    }

    private void saveCategories(final Collection<Item> items) throws SQLException {
	// categories have unique name
	final Map<String, ItemCategory> categories = new HashMap<String, ItemCategory>();
	for (Item item : items) {
	    final ItemCategory category = item.getCategory();

	    if (!categories.containsKey(category.getName())) {
		categories.put(category.getName(), category);
	    }
	}

	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into categories(id,name) values(?,?)");

	SQLException error = null;
	try {
	    conn.setAutoCommit(false);
	    for (Map.Entry<String, ItemCategory> entry : categories.entrySet()) {
		final ItemCategory currentCategory = entry.getValue();
		preparedStatement.setInt(1, currentCategory.getId());
		preparedStatement.setString(2, currentCategory.getName());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving categories", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveClasses(final Collection<Item> items) throws SQLException {

	final Map<String, ItemClass> classes = new HashMap<String, ItemClass>();
	for (Item item : items) {
	    final ItemClass iclass = item.getItemClass();

	    if (!classes.containsKey(iclass.getName())) {
		classes.put(iclass.getName(), iclass);
	    }
	}

	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into classes(id,name) values (?,?)");
	SQLException error = null;
	try {
	    conn.setAutoCommit(false);
	    for (Map.Entry<String, ItemClass> entry : classes.entrySet()) {
		final ItemClass currentClass = entry.getValue();
		preparedStatement.setInt(1, currentClass.getId());
		preparedStatement.setString(2, currentClass.getName());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving classes", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveManufacturers(final Collection<Item> items) throws SQLException {
	final Map<String, Manufacturer> manufacturers = new HashMap<String, Manufacturer>();
	for (Item item : items) {
	    final Manufacturer manufacturer = item.getManufacturer();

	    if (!manufacturers.containsKey(manufacturer.getName())) {
		manufacturers.put(manufacturer.getName(), manufacturer);
	    }
	}

	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into manufacturers(id,name,website) values (?,?,?)");
	SQLException error = null;
	try {
	    conn.setAutoCommit(false);
	    for (Map.Entry<String, Manufacturer> entry : manufacturers.entrySet()) {
		final Manufacturer curanufacturer = entry.getValue();
		preparedStatement.setInt(1, curanufacturer.getId());
		preparedStatement.setString(2, curanufacturer.getName());
		preparedStatement.setString(3, curanufacturer.getWebSite());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving manufacturers", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveParameters(final Collection<Item> items) throws SQLException {
	final Map<String, Property> propertys = new HashMap<String, Property>();
	for (Item item : items) {
	    final List<Property> propertyItem = item.getProperties();
	    for (Property property : propertyItem) {
		if (!propertys.containsKey(property.getName())) {
		    propertys.put(property.getName(), property);
		}
	    }
	}

	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into parameters(id,name) values (?,?)");
	SQLException error = null;
	try {
	    conn.setAutoCommit(false);
	    for (Map.Entry<String, Property> entry : propertys.entrySet()) {
		final Property currentParameter = entry.getValue();
		preparedStatement.setInt(1, currentParameter.getId());
		preparedStatement.setString(2, currentParameter.getName());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving manufacturers", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveSuppliers(final Collection<Item> items) throws SQLException {
	final Map<String, Supplier> suppliers = new HashMap<String, Supplier>();
	for (Item item : items) {
	    final Supplier supplier = item.getSupplier();

	    if (!suppliers.containsKey(supplier.getName())) {
		suppliers.put(supplier.getName(), supplier);
	    }
	}

	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into suppliers values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
	SQLException error = null;
	try {
	    conn.setAutoCommit(false);
	    for (Map.Entry<String, Supplier> entry : suppliers.entrySet()) {
		final Supplier currentSupplier = entry.getValue();
		preparedStatement.setInt(1, currentSupplier.getId());
		preparedStatement.setString(2, currentSupplier.getName());
		preparedStatement.setString(3, currentSupplier.getWebSite());
		preparedStatement.setString(4, currentSupplier.getCountry());
		preparedStatement.setString(5, currentSupplier.getCity());
		preparedStatement.setString(6, currentSupplier.getAddress());
		preparedStatement.setString(7, currentSupplier.getLine1());
		preparedStatement.setString(8, currentSupplier.getLine2());
		preparedStatement.setString(9, currentSupplier.getLine3());
		preparedStatement.setString(10, currentSupplier.getFax());
		preparedStatement.setString(11, currentSupplier.getFax2());
		preparedStatement.setString(12, currentSupplier.getEmail());
		preparedStatement.setString(13, currentSupplier.getWorkinHours());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving manufacturers", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveItem(final Item item) throws SQLException {
	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into items(id,model,version_date,category_id,class_id,manufacturer_id) values (?,?,?,?,?,?)");
	SQLException error = null;

	try {
	    conn.setAutoCommit(false);
	    preparedStatement.setInt(1, item.getId());
	    preparedStatement.setString(2, item.getItemModel());
	    preparedStatement.setDate(3, new java.sql.Date(item.getVersion().getTime()));

	    preparedStatement.setInt(4, item.getCategory().getId());
	    preparedStatement.setInt(5, item.getItemClass().getId());
	    preparedStatement.setInt(6, item.getManufacturer().getId());
	    preparedStatement.execute();
	    conn.commit();

	    saveValues(item);
	    savePrices(item);
	} catch (SQLException e) {
	    LOG.error("Error saving item", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void savePrices(final Item item) throws SQLException {
	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into prices(price,price_date,item_id,supplier_id) values (?,?,?,?)");
	SQLException error = null;

	final List<Price> prices = item.getPrices();
	try {
	    conn.setAutoCommit(false);
	    for (Price price : prices) {
		preparedStatement.setFloat(1, price.getValue());
		preparedStatement.setDate(2, new java.sql.Date(price.getDate().getTime()));
		preparedStatement.setInt(3, item.getId());
		preparedStatement.setInt(4, item.getSupplier().getId());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving item", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveValues(final Item item) throws SQLException {
	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into item_values(value,item_id,parameter_id) values (?,?,?)");
	SQLException error = null;

	final List<Property> propertys = item.getProperties();
	try {
	    conn.setAutoCommit(false);
	    for (Property property : propertys) {
		preparedStatement.setString(1, property.getValue());
		preparedStatement.setInt(2, item.getId());
		preparedStatement.setInt(3, property.getId());
		preparedStatement.execute();
	    }
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving item", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }

    private void saveEventTypes() throws SQLException {
	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into event_types(id, name, description) values (?,?,?)");
	SQLException error = null;
	try {
	    conn.setAutoCommit(false);
	    preparedStatement.setInt(1, 0);
	    preparedStatement.setString(2, "OK");
	    preparedStatement.setString(3, "description");
	    conn.commit();
	    preparedStatement.setInt(1, 1);
	    preparedStatement.setString(2, "WARNING");
	    preparedStatement.setString(3, "description");
	    conn.commit();
	    preparedStatement.setInt(1, 2);
	    preparedStatement.setString(2, "ERROR");
	    preparedStatement.setString(3, "description");
	    conn.commit();
	    preparedStatement.setInt(1, 3);
	    preparedStatement.setString(2, "UNKNOWN");
	    preparedStatement.setString(3, "description");
	    conn.commit();
	} catch (SQLException e) {
	    LOG.error("Error saving event types", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}
	if (error != null) {
	    throw error;
	}
    }

    private void saveSessions(final Collection<Session> sessions) throws SQLException {
	final PreparedStatement preparedStatement = conn
		.prepareStatement("insert into sessions(id, event_type_id, supplier_id, start_date, end_date, duration, items_count) "
			+ "values (?,?,?,?,?,?,?)");
	SQLException error = null;
	try {
	    for (Session session : sessions) {
		conn.setAutoCommit(false);
		preparedStatement.setInt(1, session.getId());
		preparedStatement.setInt(2, 0);
		preparedStatement.setInt(3, session.getSupplierID());
		preparedStatement.setDate(4, new java.sql.Date(session.getStartDate().getTime()));
		preparedStatement.setDate(5, new java.sql.Date(session.getEndDate().getTime()));
		preparedStatement.setTime(6, new java.sql.Time(10));
		preparedStatement.setInt(7, session.getItemsCount());
		conn.commit();
	    }
	} catch (SQLException e) {
	    LOG.error("Error saving item", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}
	if (error != null) {
	    throw error;
	}
    }

    // Method is used for testing purposes only, for in-memory db.
    @SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
    private void deleteRows(final String tableName) throws SQLException {
	final PreparedStatement preparedStatement = conn.prepareStatement("delete from "
		+ tableName);
	SQLException error = null;
	try {
	    preparedStatement.execute();
	} catch (SQLException e) {
	    LOG.error("Error deleting rows from table", e);
	    error = e;
	} finally {
	    preparedStatement.close();
	}

	if (error != null) {
	    throw error;
	}
    }
}
